/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package taskmanager2ndedition.server;

import taskmanager2ndedition.User;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import taskmanager2ndedition.ClientUserInfo.UserState;
import taskmanager2ndedition.TaskInfo.TaskState;
import taskmanager2ndedition.exceptions.ObjectNotFoundException;

/**
 *
 * @author Pavel
 */
public class MySQLUserStorage implements Storage<User> {

//    table Users in MySQL database and numbers of columns
// 1 id: INTEGER; = primary key
// 2 login: VARCHAR
// 3 password: BIGINT
// 4 admin: BIT
// 5 modified: BIT
// 6 state: INTEGER
    private static java.sql.Connection conn;

    /**
     * Initializes connection 
     * 
     * @throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException
     */
    public MySQLUserStorage() {
        try {
            String driver = "com.mysql.jdbc.Driver";
            String url = "jdbc:mysql://localhost:3306/";
            String dbName = "TaskManagerDatabase";
            String userName = "root";
            String password = "";
            Class.forName(driver).newInstance();
            conn = DriverManager.getConnection(url + dbName, userName, password);
        } catch (SQLException ex) {
            Logger.getLogger(MySQLUserStorage.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(MySQLUserStorage.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IllegalAccessException ex) {
            Logger.getLogger(MySQLUserStorage.class.getName()).log(Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            Logger.getLogger(MySQLUserStorage.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     * Deletes the specified user from the database
     * 
     * @param id    User's id that should be deleted
     *  
     */
    public synchronized void remove(int id) throws ObjectNotFoundException {
        try {
            Statement stmtUser = conn.createStatement();
            stmtUser.executeUpdate("DELETE FROM Users WHERE id = '" + id + "'");
        } catch (SQLException ex) {
            throw new ObjectNotFoundException();
        }
    }

    /**
     * Loads the specified user from the database
     * 
     * @param id  User's id which should be gotten
     * @return The specified user from the storage     
     */
    public synchronized User get(int id) throws ObjectNotFoundException {
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<User> result = new ArrayList<User>();
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT * FROM Users WHERE id = '" + id + "'");
            if (!rs.next())
                throw new ObjectNotFoundException();
            String login = rs.getString(2);
            long password = rs.getLong(3);
            boolean admin = rs.getBoolean(4);
            User user = new User(login, password, admin, id);
            user.setModified(rs.getBoolean(5));
            switch (rs.getInt(6)){
                case 1:
                    user.setState( User.UserState.Removed);
                    break;
                case 0:
                default:
                    user.setState( User.UserState.Added);
            }
            result.add(user);
        } catch (SQLException ex) {
            Logger.getLogger(MySQLUserStorage.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                rs.close();
            } catch (SQLException ex) {
                Logger.getLogger(MySQLUserStorage.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return result.get(0);
    }

    /**
     * Loads the specified user from the database
     * 
     * @param login   User's login which should be gotten
     * @return The specified user from the storage     
     */
    public synchronized User get(String login) throws java.lang.IndexOutOfBoundsException, ObjectNotFoundException {
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<User> result = new ArrayList<User>();
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT * FROM Users WHERE login = '" + login + "'");
            if (!rs.next())
                throw new ObjectNotFoundException();
            
            int id = rs.getInt(1);
            long password = rs.getLong(3);
            boolean admin = rs.getBoolean(4);
            User user = new User(login, password, admin, id);
            user.setModified(rs.getBoolean(5));
            switch (rs.getInt(6)){
                case 1:
                    user.setState( User.UserState.Removed);
                    break;
                case 0:
                default:
                    user.setState( User.UserState.Added);
            }
            result.add(user);
        } catch (SQLException ex) {
            Logger.getLogger(MySQLUserStorage.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                rs.close();
                return result.get(0);
            } catch (SQLException ex) {
                Logger.getLogger(MySQLUserStorage.class.getName()).log(Level.SEVERE, null, ex);
                return null;
            } 
        }
    }

    /**
     * Saves a new user into the database
     * 
     * @param user    User that should be saved
     *  
     */
    public synchronized void add(User user) {
        try {
            Statement stmt = conn.createStatement();
            int state = 0;
            if (user.getState() == UserState.Added) state = 0;
            else if (user.getState() == UserState.Removed) state = 1; 
            stmt.executeUpdate("INSERT INTO Users (id,login,password,admin,modified,state) VALUES ('" + user.getId() + "','" + user.getLogin()
                    + "','" + user.getPassword() + "'," + user.isAdmin() + "," + user.isModified() + ",'" + state + "')");
        } catch (SQLException ex) {
            Logger.getLogger(MySQLUserStorage.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     * Updates the specified user from the database
     * 
     * @param user    User that should be updated
     *  
     */
    public synchronized void update(User user) {
        // Probably by id is better? Probably not
        try {
            Statement stmt = conn.createStatement();
            int state = 0;
            if (user.getState() == UserState.Added) state = 0;
            else if (user.getState() == UserState.Removed) state = 1; 
            stmt.executeUpdate("UPDATE Users SET id = '" + user.getId()
                    + "',login = '" + user.getLogin() + "',password = '"
                    + user.getPassword() + "',admin = " + user.isAdmin() + ",modified = " + user.isModified() + ",state = '" + state + "' WHERE id = '" + user.getId() + "'");
        } catch (SQLException ex) {
            Logger.getLogger(MySQLUserStorage.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     * Loads all users from the database
     * 
     * @return List of all the users in the storage     
     */
    public synchronized List<User> getAll() {
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<User> result = new ArrayList<User>();
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT * FROM Users");
            while (rs.next()) {
                
                int id = rs.getInt(1);
                String login = rs.getString(2);
                long password = rs.getLong(3);
                boolean admin = rs.getBoolean(4);
                User user = new User(login, password, admin, id);
                user.setModified(rs.getBoolean(5));
                switch (rs.getInt(6)){
                case 1:
                    user.setState( User.UserState.Removed);
                    break;
                case 0:
                default:
                    user.setState( User.UserState.Added);
            }
                result.add(user);
            }
        } catch (SQLException ex) {
            Logger.getLogger(MySQLUserStorage.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                rs.close();
            } catch (SQLException ex) {
                Logger.getLogger(MySQLUserStorage.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return result;
    }

    /**
     * Saves all the users into the database
     * 
     * @param list    List of users that should be saved
     *  
     */
    public synchronized void saveAll(List<User> list) {
        try {
            for (User user : list) {
                Statement stmt = conn.createStatement();
                int state = 0;
                if (user.getState() == UserState.Added) state = 0;
                else if (user.getState() == UserState.Removed) state = 1; 
                stmt.executeUpdate("INSERT INTO Users (id,login,password,admin,modified,state) VALUES ('" + user.getId() + "','" + user.getLogin()
                        + "','" + user.getPassword() + "'," + user.isAdmin() + "," + user.isModified() + ",'" + state + "')");
            }
        } catch (SQLException ex) {
            Logger.getLogger(MySQLUserStorage.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public synchronized List<User> getGroup(int id) {
        throw new UnsupportedOperationException("Not supported yet.");
    }
}
